package com.gthncz;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.gthncz.beans.StudentMarks;
import com.gthncz.beans.StudentMarksDAO;

public class StudentMarkDAOImpl implements StudentMarksDAO {
	
	private JdbcTemplate jdbcTemplate;
	private PlatformTransactionManager transactionManager;
	
	protected class StudentMarksMapper implements RowMapper<StudentMarks> {

		@Override
		public StudentMarks mapRow(ResultSet rs, int rowNum) throws SQLException {
			StudentMarks studentMarks = new StudentMarks();
			studentMarks.setName(rs.getString("name"));
			studentMarks.setAge(rs.getInt("age"));
			studentMarks.setMarks(rs.getInt("marks"));
			studentMarks.setYear(rs.getInt("year"));
			studentMarks.setId(rs.getInt("id"));
			studentMarks.setSid(rs.getInt("sid"));
			return studentMarks;
		}
		
	}

	@Override
	public void setDataSource(DataSource dataSource) {
		// org.springframework.jdbc.datasource.DriverManagerDataSource
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public void setTransactionManager(PlatformTransactionManager transactionManager) {
		// org.springframework.jdbc.datasource.DataSourceTransactionManager
		this.transactionManager = transactionManager;
	}

	@Override
	public void insert(String name, int age, int marks, int year) {
		TransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
		TransactionStatus transactionStatus = this.transactionManager.getTransaction(transactionDefinition);
		
		try {
			System.out.printf("Insert new record(%s, %d) to student . ", name, age );
			String sql = "INSERT INTO `tb_student`(`name`, `age`) VALUES(?, ?)";
			jdbcTemplate.update(sql, name, age);
			sql = "SELECT MAX(`id`) FROM `tb_student`";
			int sid = jdbcTemplate.queryForObject(sql, Integer.class); // queryForInt 方法不存在。。
			System.out.printf("Insert new record(%d, %d) to marks . \n", marks, year);
			sql = "INSERT INTO `tb_marks`(`sid`, `marks`, `year`) VALUES(?, ?, ?)";
			jdbcTemplate.update(sql, sid, marks, year);
			transactionManager.commit(transactionStatus);
		} catch (Exception e) {
			System.out.println("Occur errors during execution. Rollback status.");
			e.printStackTrace();
			transactionManager.rollback(transactionStatus);
		}
	}

	@Override
	public List<StudentMarks> listStudents() {
		String sql = "SELECT `A`.*, `B`.* FROM `tb_student` AS `A` INNER JOIN `tb_marks` AS `B` ON `A`.`id`=`B`.`sid` ";
		List<StudentMarks> students = jdbcTemplate.query(sql, new StudentMarksMapper());
		return students;
	}

}
